package bbs2.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import bbs2.model.File;

public class FileDAO {
    
    private static File makeFile(ResultSet rs) throws Exception {
        File comment = new File();
        comment.setId(rs.getInt("ID"));
        comment.setUserId(rs.getInt("userId"));
        comment.setTargetType(rs.getInt("targetType"));
        comment.setTargetId(rs.getInt("targetId"));
        comment.setFileName(rs.getString("fileName"));
        comment.setFileSize(rs.getInt("fileSize"));
        comment.setFileTime(rs.getTimestamp("fileTime"));
        return comment;
    }    
    
    public static ArrayList<File> select(int targetType, int targetId) throws Exception {
        ArrayList<File> list = new ArrayList<File>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DB.getConnection();
            String sql = "SELECT [id], userId, targetType, targetId, fileName, fileSize, fileTime FROM [File] " +
                         "WHERE targetType=? and targetId=?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, targetType);
            statement.setInt(2, targetId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) 
                list.add(makeFile(resultSet));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return list;
    }       
    
    public static int insert(int targetType, int targetId, int userId, String fileName, int fileSize, InputStream inputStream) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet generatedKeys = null;
        try {
            conn = DB.getConnection();        
            String sql = "INSERT INTO [File] ([targetType], [targetId], [userId], [fileName], [fileSize], [fileTime], [data]) VALUES (?, ?, ?, ?, ?, GETDATE(), ?)";
            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            
            stmt.setInt(1, targetType);
            stmt.setInt(2, targetId);
            stmt.setInt(3, userId);
            stmt.setString(4, fileName);
            stmt.setInt(5, fileSize);
            stmt.setBinaryStream(6, inputStream);
            stmt.executeUpdate();
            generatedKeys = stmt.getGeneratedKeys();
            generatedKeys.next();
            return generatedKeys.getInt(1);
        } finally {
            if (generatedKeys != null) generatedKeys.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }
    }  

    public static void delete(int id) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "DELETE [File] WHERE [id] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }        
    }
    
}
